CREATE DYNAMIC EXCEL DASHBOARDS: A simplified step-by-step guide by William K. Wonder

Author:William K. Wonder [Wonder, William K.]
Language: eng
Format: azw3, epub
Publisher: UNKNOWN
Published: 2017-10-26T04:00:00+00:00

Figure 23: Steps to create the table and subsequent slicer

2. In the Drivers worksheet, insert a pivot table based on this table. Drag the Top Views column on the Filter section of the pivot report grid. Put nothing on the Row, Column, or Values sections.

3. Insert a slicer and select the Top Views column. Select one of the options on the slicer, then cut and paste the slicer on the dashboard page.

4. Select cell M2 shown in figure 23 above and on the Name box, name this cell as selView.

5. On the main report (refer to figure 18 above), create a column to rank the products by revenue.

Note: This is a critical step since the user (the Hotel Manager) will control how the ranking is done as s/he selects top or bottom from the slicer Jean has just created.

Jean enters the following rank formula in the Calculations worksheet, cell W51.

=RANK ($T51, $T$51:$T$119, 0)

This formula will rank the revenues in descending order (defined by the 0 -zero- as the last parameter of this formula). Jean realizes that this part of the formula needs to be dynamic so that it can toggle between descending and ascending as the order of the ranking. To make this dynamic, she makes the adjustment as follows:

=RANK ($T51, $T$51:$T$119, IF(selView="Top 5 Products", 0, 1))

The formula uses an IF function to check what the user has selected on the dashboard, that is whether top or bottom, and uses this to return a 0 or a 1 for top and bottom views respectively.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.